#!/bin/bash
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else
   echo "请传入日期参数"
   exit
fi

dws_finishcourse_num_1d="
set hive.strict.checks.type.safety=false;
insert overwrite table dws_finishcourse_num_1d partition (dt='${do_date}')
select
    t3.course_id, course_complete_num, chapter_complete_num
    from
(select t1.course_id,
        sum(if(user_chapter_num = chapter_num, 1, 0)) course_complete_num,
        '${do_date}'
 from (select course_id,
              id,
              count(1) user_chapter_num
       from dwd_user_video_inc
       where position_sec / during_sec >= 0.9
         and dt = '${do_date}'
       group by course_id, id) t1
          join
          (select * from ods_course_info_full where dt = '${do_date}') t2
          on t1.course_id = t2.id
 group by t1.course_id
)t3
join
(select course_id,
        count(1) chapter_complete_num
 from dwd_user_video_inc
 where position_sec / during_sec >= 0.9
   and dt = '${do_date}'
 group by course_id
)t4
on t3.course_id=t4.course_id;
"

dws_test_exam_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_test_exam_1d partition(dt)
select paper_id,
       paper_title,
       course_id,
       course_name,
       count(1) num,
       count(distinct user_id) user_num,
       sum(score) score_sum,
       sum(duration_sec) duration_sec_sum,
       dt
from dwd_test_exam_inc
where dt<date_add('${do_date}',1)
group by paper_id, paper_title,course_id,course_name,dt;
"

dws_test_course_scorestage_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_test_course_scorestage_1d partition(dt)
select course_id,
       course_name,
       score_stage,
       count(distinct user_id) user_num,
       count(*) num,
       dt
from dwd_test_exam_inc
where dt<date_add('${do_date}',1)
group by course_id, course_name,score_stage,dt;
"

dws_test_question_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_test_question_1d partition(dt)
 select question_id,
        question_txt,
        sum(if(is_correct = '1',1,0)) correct_num,
        count(1) total_num,
        dt
 from dwd_test_exam_inc
 where dt<date_add('${do_date}',1)
 group by question_id, question_txt,dt;
 "
dws_order_category_lidan_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_order_category_lidan_1d partition(dt)
select a.id,
       b.category_name,
       a.user_id,
       a.final_amount,
       a.dt
from dwd_order_detail_lidan_inc a
     left join dim_course_info_lidan_full b
               on a.course_id=b.id
where a.dt<='${do_date}' and b.dt='${do_date}';
"

dws_order_subject_lidan_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_order_subject_lidan_1d partition(dt)
select a.id,
       b.subject_name,
       a.user_id,
       a.final_amount,
       a.dt
from dwd_order_detail_lidan_inc a
     left join dim_course_info_lidan_full b
               on a.course_id=b.id
where a.dt<='${do_date}' and b.dt='${do_date}';
"
dws_order_course_lidan_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_order_course_lidan_1d partition(dt)
select a.id,
       b.course_name,
       a.user_id,
       a.final_amount,
       a.dt
from dwd_order_detail_lidan_inc a
     left join dim_course_info_lidan_full b
               on a.course_id=b.id
where a.dt<='${do_date}' and b.dt='${do_date}';
"
dws_review_info_course_lidan_1d="
insert overwrite table dws_review_info_course_lidan_1d partition(dt='${do_date}')
select b.course_name,
       sum(if(a.dt='${do_date}',a.review_stars,0)) total_review_score_1d,
       count(distinct if(a.dt='${do_date}',a.user_id,null)) review_user_num_1d,
       count(if(a.dt='${do_date}',a.id,null)) count_review_1d,
       count(if(a.dt='${do_date}' and review_stars='5',a.id,null)) count_good_review_1d,
       sum(if(a.dt>=date_add('${do_date}',-6),a.review_stars,0)) total_review_score_7d,
       count(distinct if(a.dt>=date_add('${do_date}',-6),a.user_id,null)) review_user_num_7d,
       count(if(a.dt>=date_add('${do_date}',-6),a.id,null)) count_review_7d,
       count(if(a.dt>=date_add('${do_date}',-6) and review_stars='5',a.id,null)) count_good_review_7d,
       sum(if(a.dt>=date_add('${do_date}',-29),a.review_stars,0)) total_review_score_30d,
       count(distinct if(a.dt>=date_add('${do_date}',-29),a.user_id,null)) review_user_num_30d,
       count(if(a.dt>=date_add('${do_date}',-29),a.id,null)) count_review_30d,
       count(if(a.dt>=date_add('${do_date}',-29) and review_stars='5',a.id,null)) count_good_review_30d
from dwd_review_info_lidan_inc a
     left join dim_course_info_lidan_full b ON a.course_id=b.id
where a.dt>=date_add('${do_date}',-29) AND a.dt<='${do_date}' and b.dt='${do_date}'
group by b.course_name;
"
dws_user_retention_category_lidan_1d="
with category_order as(
	select t1.id,
	       t1.course_id,
	       t1.course_name,
	       t2.subject_name,
	       t2.category_name,
	       t1.order_id,
	       t1.user_id,
	       t1.origin_amount,
	       t1.coupon_reduce,
	       t1.final_amount,
	       t1.create_time,
	       t1.update_time,
	       t1.dt
	from dwd_order_detail_lidan_inc t1
	     left join dim_course_info_lidan_full t2 on t1.course_id=t2.id
	WHERE t1.dt<='${do_date}' and t2.dt='${do_date}'
                     )


insert overwrite table dws_user_retention_category_lidan_1d partition(dt='${do_date}')

select a.category_name,
       a.user_id,
       a.create_time,
       b.final_amount,
       b.min_order_create_time,
       datediff(b.min_order_create_time,a.create_time)+1,
       a.dt
from
	(
		select t2.category_name,
		       t1.user_id,
		       min(t1.create_time) create_time,
		       t1.dt
		from dwd_user_chapter_process_lidan_inc t1
		left join dim_course_info_lidan_full t2 on t1.course_id=t2.id
		where t1.dt <= '${do_date}'
		  and t1.dt >= date_add('${do_date}', -6) and t2.dt='${do_date}'
	    group by t2.category_name,t1.user_id,t1.dt
	)a
left join(
	         select category_name,
	                user_id,
	                final_amount,
	                min_order_create_time
	         from(
		             select category_name,
		                    user_id,
		                    final_amount,
		                    create_time,
		                    min(create_time) over(partition by category_name,user_id) min_order_create_time
		             from category_order
		             where dt<='${do_date}' ) t1
	         where create_time=min_order_create_time
	         )b
on a.user_id=b.user_id and a.category_name=b.category_name;
"
dws_user_retention_subject_lidan_1d="
with subject_order as(
	select t1.id,
	       t1.course_id,
	       t1.course_name,
	       t2.subject_name,
	       t2.category_name,
	       t1.order_id,
	       t1.user_id,
	       t1.origin_amount,
	       t1.coupon_reduce,
	       t1.final_amount,
	       t1.create_time,
	       t1.update_time,
	       t1.dt
	from dwd_order_detail_lidan_inc t1
	     left join dim_course_info_lidan_full t2 on t1.course_id=t2.id
	WHERE t1.dt<='${do_date}' and t2.dt='${do_date}'
                     )


insert overwrite table dws_user_retention_subject_lidan_1d partition(dt='${do_date}')

select a.subject_id,
       a.subject_name,
       a.user_id,
       a.create_time,
       b.final_amount,
       b.min_order_create_time,
       datediff(b.min_order_create_time,a.create_time)+1,
       a.dt
from
	(
		select t2.subject_id,
		       t2.subject_name,
		       t1.user_id,
		       min(t1.create_time) create_time,
		       t1.dt
		from dwd_user_chapter_process_lidan_inc t1
		left join dim_course_info_lidan_full t2 on t1.course_id=t2.id
		where t1.dt <= '${do_date}'
		  and t1.dt >= date_add('${do_date}', -6) and t2.dt='${do_date}'
	    group by t2.subject_id,t2.subject_name,t1.user_id,t1.dt
	)a
left join(
	         select subject_name,
	                user_id,
	                final_amount,
	                min_order_create_time
	         from(
		             select subject_name,
		                    user_id,
		                    final_amount,
		                    create_time,
		                    min(create_time) over(partition by subject_name,user_id) min_order_create_time
		             from subject_order
		             where dt<='${do_date}' ) t1
	         where create_time=min_order_create_time
	         )b
on a.user_id=b.user_id and a.subject_name=b.subject_name;
"
dws_user_retention_course_lidan_1d="
insert overwrite table dws_user_retention_course_lidan_1d partition(dt='${do_date}')
select a.course_id,
       c.course_name,
       a.user_id,
       a.create_time,
       b.final_amount,
       b.min_order_create_time,
       datediff(b.min_order_create_time,a.create_time)+1,
       a.dt
from
	(
		select course_id,
		       user_id,
		       min(create_time) create_time,
		       dt
		from dwd_user_chapter_process_lidan_inc
		where dt <= '${do_date}'
		  and dt >= date_add('${do_date}', -6)
	    group by course_id,user_id,dt
	)a
left join(
	         select course_id,
	                user_id,
	                final_amount,
	                min_order_create_time
	         from(
		             select course_id,
		                    user_id,
		                    final_amount,
		                    create_time,
		                    min(create_time) over(partition by course_id,user_id) min_order_create_time
		             from dwd_order_detail_lidan_inc
		             where dt<='${do_date}' ) t1
	         where create_time=min_order_create_time
	         )b
on a.user_id=b.user_id and a.course_id=b.course_id
left join dim_course_info_lidan_full c ON a.course_id=c.id
where c.dt='${do_date}';
"
dws_trade_user_province_order_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_user_province_order_1d partition (dt)
select user_id,
       province_id,
       province_name,
       area_code,
       iso_code,
       iso_3166_2,
       cast(order_count_1d as BIGINT),
       cast(order_num_1d as BIGINT),
       cast(order_original_amount_1d as DECIMAL(16,2)),
       cast(coupon_reduce_amount_1d as DECIMAL(16,2)),
       cast(order_total_amount_1d as DECIMAL(16,2)),
       dt
from (
         select user_id,
                province_id,
                count(distinct order_id) order_count_1d,
                count(order_id)          order_num_1d,
                sum(split_origin_amount) order_original_amount_1d,
                sum(split_coupon_reduce) coupon_reduce_amount_1d,
                sum(split_total_amount) order_total_amount_1d,
                dt
         from dwd_trade_order_detail_inc
         where dt <= '${do_date}'
         group by user_id, province_id, dt
     ) t1 left join (
         select id,
                province_name,
                area_code,
                iso_code,
                iso_3166_2,
                region_id
         from dim_province_full
         where dt = '${do_date}'
    ) t2 on t1.province_id = t2.id;
"



case $1 in
    "dws_test_exam_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_test_exam_1d"
    ;;
    "dws_test_course_scorestage_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_test_course_scorestage_1d"
    ;;
    "dws_test_question_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_test_question_1d"
    ;;
    "dws_order_category_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_order_category_lidan_1d"
    ;;
    "dws_order_subject_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_order_subject_lidan_1d"
    ;;
    "dws_order_course_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_order_course_lidan_1d"
    ;;
    "dws_review_info_course_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_review_info_course_lidan_1d"
    ;;
    "dws_user_retention_category_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_user_retention_category_lidan_1d"
    ;;
    "dws_user_retention_subject_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_user_retention_subject_lidan_1d"
    ;;
    "dws_user_retention_course_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_user_retention_course_lidan_1d"
    ;;
    "dws_trade_user_province_order_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_trade_user_province_order_1d"
    ;;
    "dws_finishcourse_num_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_finishcourse_num_1"


    "all" )
        /opt/module/hive/bin/hive -e "
        use ${APP};
        $dws_test_exam_1d
        $dws_test_course_scorestage_1d
        $dws_test_question_1d
        $dws_order_category_lidan_1d
        $dws_order_subject_lidan_1d
        $dws_order_course_lidan_1d
        $dws_review_info_course_lidan_1d
        $dws_user_retention_category_lidan_1d
        $dws_user_retention_subject_lidan_1d
        $dws_user_retention_course_lidan_1d
        $dws_trade_user_province_order_1d
        $dws_finishcourse_num_1d
        "
    ;;
esac